Create Inner Join in Pipeline
Introduction
An INNER JOIN is such type of join that returns all rows from both the participating tables where the key record of one table is equal to the key records of another table. This type of join required a comparison operator to match rows from the participating tables based on a common field or column of both the tables.
From Pipeline Inner join you will learn to create a Pipeline that contains two tables having joined from two or more rows. The inner join selects all the same rows of the tables.
Prerequisites
To Schedule a Pipeline, you need working Parameter Datasets(JDBC) and a list of tables and views.
Objective
How to Create Inner join in Pipeline.
Download Pipeline.zip file from the link given Pipeline.zip
Follow steps to create Inner-Join Pipeline in AIV:-
Login to the AIV application with valid user credentials.
Go to Master Data > Dataset section from the Hamburger menu.
Go to the Dataset section, Click on Create Pipeline from the bottom menu bar. As shown in the figure below:
Once you click, you can see a blank create pipeline window as in the below image:
Enter the Following detail in the Pipeline dialog box:
Name: Inner Join
Source: ClassicModels (select as per your requirement)
Expand tables from the left side table view, As shown in the figure below:
Drag and Drop the OFFICES table from the Table list to the Right side diagram box, then select all the columns of the OFFICES table, as shown in the figure below:
Click on submit button, After submitting, the diagram box will look like the figure below:
Drag and drop the CUSTOMERS from the left table menu to the right diagram box.
Select all the columns from the Customers table, and click submit button.
After adding OFFICES and CUSTOMERS from the table list, Drag the COUNTRY column from Offices to Customers, as shown in the figure below:
Click on the join between two tables, to add Pipeline configuration.
The Pipeline configuration control box will open, Select the Inner Join radio button and click on Submit button, as shown in the figure below:
Left Node should have OFFICES and Right Node should have CUSTOMERS.
Click on Submit button. The Connection between the two tables will look as shown in the figure below:
Click on Submit to save the Pipeline.
The pipeline is run in the Adhoc Analysis.
Go to Documents > Adhoc Analysis section from Hamburger menu.
In Adhoc Analysis click on the + icon to create a new Analysis, as shown in the figure below:
When you click on create Analysis then the Analysis Selection dialog box will open, as shown in the figure below:
Enter the following details in Analysis Section:
Dataset: Customers (select the Pipeline, which you are created)
The two tables will get load the left side box of Pipeline view, Now expends the OFFICES table.
Drag and drop a COUNTRY column from OFFICES to Rows, Drag and drop Territory column from OFFICE and Put it in Column box, Drag and drop the Credit Limit column from Customers to Values box (Select sum in Type), as shown in the figure below:
Click on Preview Button it will show the output and after the preview click on Cancel Button it will show the screen as follows:
Click on Save as
icon for saving the Analysis, it will show the screen as below:
Give the name of Analysis.
Name: Customer Pipeline and Select the folder where you want to save Analysis, as shown in the figure below:
Click on Save As button to save Analysis.